/**
 * 
 */
package org.compiere.report;

import java.io.File;
import java.io.FileOutputStream;
import java.math.BigDecimal;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;
import java.util.logging.Level;

import org.adempiere.exceptions.AdempiereException;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.compiere.model.MAttachmentEntry;
import org.compiere.model.MBPartner;
import org.compiere.process.BalanceSheets;
import org.compiere.process.ProcessInfo;
import org.compiere.process.ProcessInfoParameter;
import org.compiere.process.SvrProcess;
import org.compiere.util.CLogger;
import org.compiere.util.DB;
import org.compiere.util.Env;
import org.compiere.util.Msg;

import extend.org.compiere.utils.Util;

/**
 * @author A.Kulantayev
 * Beta version 
 */
public class HR_ReportOSVofAccount extends SvrProcess {
	private Properties m_ctx;
	private ProcessInfo pi;
	private static CLogger s_log = CLogger.getCLogger(BalanceSheets.class);
	private int p_Account_ID = 0;
	/** Period Parameter */
	private Timestamp p_DateAcct_From = null;
	private Timestamp p_DateAcct_To = null;
	/**Out file */
	String ResultPath;
	@Override
	protected void prepare() {
		m_ctx = Env.getCtx();
		pi = getProcessInfo();
		ProcessInfoParameter[] para = getParameter();
		for (int i = 0; i < para.length; i++) {
			String name = para[i].getParameterName();
			if (para[i].getParameter() == null)
				;
			else if (name.equals("DateAcct")) {
				p_DateAcct_From = (Timestamp) para[i].getParameter();
				p_DateAcct_To = (Timestamp) para[i].getParameter_To();
			}
			else if (name.equals("Account_ID"))
				p_Account_ID = ((BigDecimal) para[i].getParameter()).intValue();
			else
				log.log(Level.SEVERE, "Unknown Parameter: " + name);
		}
		if (p_DateAcct_To == null)
			p_DateAcct_To = p_DateAcct_From;
	}
	@Override
	protected String doIt() throws Exception {
		//File path
		String p_SQl = "SELECT erps_periodrange("+DB.TO_DATE(p_DateAcct_From, true)+","+DB.TO_DATE(p_DateAcct_To, true)+") WHERE 1=?";
		String periodName = DB.getSQLValueString(get_TrxName(), p_SQl, 1);
		ResultPath = Util.getResultPath("ОСВ_за_"+periodName, (new String("ОСВ_за_"+periodName).length())+1);
		// 
		  MAttachmentEntry entry = Util.getAttachment(pi, m_ctx, ResultPath);
		if (entry == null)
			throw new AdempiereException(Msg.translate(m_ctx,"NotFoundTemplate"));
		
		XSSFWorkbook wb = new XSSFWorkbook(entry.getInputStream());
		XSSFSheet sheet = wb.getSheetAt(0);
		Row R = null;
		//Title
		String accValue = DB.getSQLValueString(get_TrxName(), "SELECT Value FROM C_ElementValue WHERE C_ElementValue_ID = ?", p_Account_ID);
		sheet.getRow(1).getCell(0).setCellValue("Оборотно-сальдовая ведомость по счету "+accValue+" за "+periodName);
		//
		List<Data> list = new ArrayList<Data>();
		String sql = "WITH main AS (SELECT c_bpartner_id,SUM(amtacctdr) as dr1, SUM(amtacctcr) as cr1,0::numeric as dr2, 0::numeric as cr2"+ 
				 "\nFROM fact_acct"+
				 "\nWHERE dateacct<'"+p_DateAcct_From+"' AND account_id="+p_Account_ID+" AND c_bpartner_id IS NOT NULL"+ 
				 "\nGROUP BY c_bpartner_id"+ 
				 "\nUNION ALL"+
				 "\nSELECT c_bpartner_id,0::numeric as dr1,0::numeric as cr1,SUM(amtacctdr) as dr2, SUM(amtacctcr) as cr2"+ 
				 "\nFROM fact_acct"+
				 "\nWHERE dateacct BETWEEN '"+p_DateAcct_From+"' AND '"+p_DateAcct_To+"' AND account_id="+p_Account_ID+//" AND c_bpartner_id IS NOT NULL"+  
				 "\nGROUP BY c_bpartner_id)"+
				"\nSELECT m.c_bpartner_id,SUM(dr1) as dr1,SUM(cr1) as cr1, SUM(dr2) as dr2, SUM(cr2) as cr2"+
				"\nFROM main m"+ 
//				"\nWHERE dr1 > 0 OR cr1 > 0 OR dr2 > 0 OR cr2 > 0"+
//				"\nLEFT JOIN c_bpartner b ON m.c_bpartner_id = b.c_bpartner_id"+
//				"\nWHERE b.isEmployee = 'Y' AND (SELECT COUNT(*) FROM HR_Employee WHERE IsActive='Y' AND C_BPartner_ID = m.c_bpartner_id)>0"+
				"\nGROUP BY m.c_bpartner_id"+
				"\nORDER BY (SELECT Name FROM C_BPartner WHERE C_BPartner.C_BPartner_ID = m.C_BPartner_ID)";
		
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try{
			pstmt = DB.prepareStatement(sql,null);
			rs = pstmt.executeQuery();
			while(rs.next()){
				Data line = new Data();
				line.setId(rs.getInt(1));
				line.setbSumDR(rs.getBigDecimal(2));
				line.setbSumCR(rs.getBigDecimal(3));
				line.settSumDR(rs.getBigDecimal(4));
				line.settSumCR(rs.getBigDecimal(5));
				list.add(line);
			}
		}
		catch (SQLException e)	{
			s_log.log(Level.SEVERE, "Account", e);
		}
		finally
		{
			DB.close(rs, pstmt);
			rs = null; pstmt = null;
		}
		int rowNo = 5;
		for(Data result : list.toArray(new Data[list.size()])){
			R = sheet.createRow(rowNo);
			R.createCell(0).setCellValue(result.getId()+"-"+ new MBPartner(m_ctx, result.getId(), get_TrxName()).getName());
			R.getCell(0).setCellStyle(sheet.getRow(3).getCell(8).getCellStyle());			

			for(int s=1;s<7;s++)
				R.createCell(s).setCellStyle(sheet.getRow(3).getCell(7).getCellStyle());
			
			//edit
			if(result.getbSumDR().compareTo(result.getbSumCR())==1) //если Дебет > Кредит
			{
				R.getCell(1).setCellValue(result.getbSumDR().add(result.getbSumCR().negate()).doubleValue());
			}
			if(result.getbSumDR().compareTo(result.getbSumCR())==-1) //если Дебет < Кредит
			{
				R.getCell(2).setCellValue(result.getbSumCR().add(result.getbSumDR().negate()).doubleValue());
			}
			R.getCell(3).setCellValue(result.gettSumDR().doubleValue());
			R.getCell(4).setCellValue(result.gettSumCR().doubleValue());
			
			BigDecimal endDR = result.getbSumDR().add(result.gettSumDR());
			BigDecimal endCR = result.getbSumCR().add(result.gettSumCR());
			
			if(endDR.compareTo(endCR)==1)
				R.getCell(5).setCellValue(endDR.add(endCR.negate()).doubleValue());
			if(endDR.compareTo(endCR)==-1)
				R.getCell(6).setCellValue(endCR.add(endDR.negate()).doubleValue());
			//
			rowNo++;
		}
		R = sheet.createRow(rowNo);
		R.createCell(0).setCellValue("Итого"); //Total name
		R.getCell(0).setCellStyle(sheet.getRow(3).getCell(0).getCellStyle());
		R.createCell(1).setCellFormula("SUM(B3:B" + rowNo + ")"); //Total debet begin
		R.getCell(1).setCellStyle(sheet.getRow(3).getCell(10).getCellStyle());
		R.createCell(2).setCellFormula("SUM(C3:C" + rowNo + ")"); //Total credit begin
		R.getCell(2).setCellStyle(sheet.getRow(3).getCell(10).getCellStyle());
		R.createCell(3).setCellFormula("SUM(D3:D" + rowNo + ")"); //Total turnover debet
		R.getCell(3).setCellStyle(sheet.getRow(3).getCell(10).getCellStyle());
		R.createCell(4).setCellFormula("SUM(E3:E" + rowNo + ")"); //Total turnover credit 
		R.getCell(4).setCellStyle(sheet.getRow(3).getCell(10).getCellStyle());
		R.createCell(5).setCellFormula("SUM(F3:F" + rowNo + ")"); // Total debet end
		R.getCell(5).setCellStyle(sheet.getRow(3).getCell(10).getCellStyle());
		R.createCell(6).setCellFormula("SUM(G3:G" + rowNo + ")"); // Total credit end
		R.getCell(6).setCellStyle(sheet.getRow(3).getCell(10).getCellStyle());
		
		sheet.getRow(3).getCell(7).setCellStyle(sheet.getRow(3).getCell(9).getCellStyle());
		sheet.getRow(3).getCell(8).setCellStyle(sheet.getRow(3).getCell(9).getCellStyle());
		sheet.getRow(3).getCell(10).setCellStyle(sheet.getRow(3).getCell(9).getCellStyle());
		sheet.getRow(3).getCell(9).setCellValue("");
		// Write the output to a file
		FileOutputStream fileOut = new FileOutputStream(ResultPath);
		wb.write(fileOut);
		fileOut.close();
		// Open the file with the application associated
		Util.launchFile(new File(ResultPath));	
		return Msg.translate(m_ctx, "Success");
	}
	
	class Data{
		int id = 0;
		BigDecimal bSumDR = null;
		BigDecimal bSumCR = null;
		BigDecimal tSumDR = null;
		BigDecimal tSumCR = null;
		public BigDecimal gettSumDR() {
			return tSumDR;
		}
		public void settSumDR(BigDecimal tSumDR) {
			this.tSumDR = tSumDR;
		}
		public BigDecimal gettSumCR() {
			return tSumCR;
		}
		public void settSumCR(BigDecimal tSumCR) {
			this.tSumCR = tSumCR;
		}
		public int getId() {
			return id;
		}
		public void setId(int id) {
			this.id = id;
		}
		public BigDecimal getbSumDR() {
			return bSumDR;
		}
		public void setbSumDR(BigDecimal sumDR) {
			this.bSumDR = sumDR;
		}
		public BigDecimal getbSumCR() {
			return bSumCR;
		}
		public void setbSumCR(BigDecimal sumCR) {
			this.bSumCR = sumCR;
		}
	}

}
